package dao.impl;

import bean.ghj.awayrecord;
import bean.ghj.changerecord;
import bean.ghj.payrecord;
import dao.recordQueryDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import util.C3p0Util;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class recordQueryDaoImpl implements recordQueryDao {

    /**
     * 查询调房记录
     * @param date1
     * @param date2
     */
    @Override
    public List<changerecord> regexChangeRecordInfo(Date date1, Date date2) throws SQLException {
        String sql = "select c_name,c_oroom_id,c_nroom_id,c_obed_id,c_nbed_id from t_changerecord c inner join t_inrecord i on c.c_id = i.i_id where date_format(i.i_stime,'%Y-%m-%d') between date_format(?,'%Y-%m-%d') and date_format(?,'%Y-%m-%d');";
        QueryRunner qr = new QueryRunner(C3p0Util.getDs());
        List<changerecord> cg = qr.query(sql,new BeanListHandler<changerecord>(changerecord.class),date1,date2);

        return cg;
    }

    /**
     * 查询缴费记录
     * @param date1
     * @param date2
     */
    @Override
    public List<payrecord> regexPayRecordInfo(Date date1, Date date2) throws SQLException {
        String sql = "select p_name,p_stime,p_rec_depo,p_pay_depo,p_cost_diff from t_payrecord p inner join t_inrecord i on p.p_id = i.i_id where date_format(i.i_stime,'%Y-%m-%d') between date_format(?,'%Y-%m-%d') and date_format(?,'%Y-%m-%d');";
        QueryRunner qr = new QueryRunner(C3p0Util.getDs());
        List<payrecord> cg = qr.query(sql,new BeanListHandler<payrecord>(payrecord.class),date1,date2);
        return cg;
    }
    /**
     * 查询离宿记录
     * @param date1
     * @param date2
     */
    @Override
    public List<awayrecord> regexAwayRecordInfo(Date date1, Date date2) throws SQLException {
        String sql = "select a_name,a_tel,a_reason,a_etime,a_rec_depo,a_pay_depo,a_ret_depo from t_awayrecord a inner join t_inrecord i on a.a_id = i.i_id where date_format(i.i_stime,'%Y-%m-%d') between date_format(?,'%Y-%m-%d') and date_format(?,'%Y-%m-%d');";
        QueryRunner qr = new QueryRunner(C3p0Util.getDs());
        List<awayrecord> cg = qr.query(sql,new BeanListHandler<awayrecord>(awayrecord.class),date1,date2);
        return cg;
    }
}
